﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using SMSModels;

namespace DAL.QueryStatistics
{
    public class BorrowGoodsQueryService
    {
        /// <summary>
        /// 获取借货表dataset
        /// </summary>
        /// <returns></returns>
        public DataSet GetDataSet()
        {
            string sql = "select 借货编号,借货单位,借货人,联系电话,借货表.货物编号,货物档案表.货物名称,规格,计量单位,借货表.仓库ID,仓库信息表.仓库名称,借货数量,经手人,借货时间,借货表.备注 from 借货表,货物档案表,仓库信息表 where 借货表.货物编号=货物档案表.货物编号 and 借货表.仓库ID=仓库信息表.仓库ID";
            return SQLHelper.GetDataSet(sql, "borrowGoodsInfo");
        }


        public List<BorrowGoods> GetBorrowGoodsInfoList()
        {
            string sql = "select 借货编号,借货单位,借货人,联系电话,借货表.货物编号,货物档案表.货物名称,规格,计量单位,借货表.仓库ID,仓库信息表.仓库名称,借货数量,经手人,借货时间,借货表.备注 from 借货表,货物档案表,仓库信息表 where 借货表.货物编号=货物档案表.货物编号 and 借货表.仓库ID=仓库信息表.仓库ID";
            SQLiteDataReader rd = SQLHelper.GetReader(sql);
            List<BorrowGoods> list = new List<BorrowGoods>();
            while (rd.Read())
            {
                list.Add(new BorrowGoods
                {
                    BorrowGoodsUnits = rd["借货单位"].ToString(),
                    //Borrower = rd["借货人"].ToString(),
                    //BorrowerPhone=rd["联系电话"].ToString(),
                    GoodsID=rd["货物编号"].ToString(),
                    GoodsName=rd["货物名称"].ToString(),
                    Spec = rd["规格"].ToString(),
                    Units = rd["计量单位"].ToString(),
                    StoreID = Convert.ToInt32(rd["仓库ID"]),
                    StoreName = rd["仓库名称"].ToString(),
                    //BorrowGoodsQuantity=Convert.ToInt32(rd["借货数量"]),
                    //Handler=rd["经手人"].ToString(),
                    //BorrowGoodsTime=rd["借货时间"].ToString(),

                    
                });
            }
            rd.Close();
            return list;
        }


        public DataSet QueryBorrowGoodsInfoOfKeyWords(string queryType, string queryKeyWords)
        {
            string sql = "select * from (select 借货编号,借货单位,借货人,联系电话,借货表.货物编号,货物档案表.货物名称,借货表.仓库ID,仓库信息表.仓库名称 as 所属仓库,规格,计量单位,借货数量,经手人,借货时间,借货表.备注 from 借货表,货物档案表,仓库信息表 where 借货表.货物编号=货物档案表.货物编号 and 借货表.仓库ID=仓库信息表.仓库ID) b";
            if (queryType == "货物编号")  //以货物ID查询
            {
                sql += string.Format(" where b.货物编号='{0}'", queryKeyWords);
            }
            if (queryType == "货物名称")    //以货物名称查询
            {
                sql += string.Format(" where b.货物名称 like '%{0}%'", queryKeyWords);
            }
            if (queryType == "仓库名称")    //以仓库名称查询
            {
                sql += string.Format(" where b.所入仓库='{0}'", queryKeyWords);
            }
            return SQLHelper.GetDataSet(sql, "queryResult");
        }
    }
}
